package com.moneyanalyzer.dao;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.moneyanalyzer.db.DatabaseHandler;
import com.moneyanalyzer.model.Budget;
import com.moneyanalyzer.model.Category;

public class BudgetDao {
	private static BudgetDao me = null;
	
	private BudgetDao(){
		
	}
	
	public static BudgetDao getInstance(){
		if(me == null)
			me = new BudgetDao();
		return me;
	}
	
	
	public List<Budget> list(Context context){
		List<Budget> budgets = null;
		SQLiteDatabase sqlLite = new DatabaseHandler(context).getReadableDatabase();
		
		String sql = "SELECT "+
						DatabaseHandler.TABLE_BUDGET+"."+DatabaseHandler.COLUMN_BUDGET_ID+" AS "+DatabaseHandler.TABLE_BUDGET+"_"+DatabaseHandler.COLUMN_BUDGET_ID+", "+ 
						DatabaseHandler.TABLE_BUDGET+"."+DatabaseHandler.COLUMN_BUDGET_VALUE+" AS "+DatabaseHandler.TABLE_BUDGET+"_"+DatabaseHandler.COLUMN_BUDGET_VALUE+", "+						
						DatabaseHandler.TABLE_BUDGET+"."+DatabaseHandler.COLUMN_BUDGET_NAME+" AS "+DatabaseHandler.TABLE_BUDGET+"_"+DatabaseHandler.COLUMN_BUDGET_NAME+", "+
						DatabaseHandler.TABLE_BUDGET+"."+DatabaseHandler.COLUMN_BUDGET_PERCENT_ALERT+" AS "+DatabaseHandler.TABLE_BUDGET+"_"+DatabaseHandler.COLUMN_BUDGET_PERCENT_ALERT+", "+
						DatabaseHandler.TABLE_BUDGET+"."+DatabaseHandler.COLUMN_BUDGET_ALERT+" AS "+DatabaseHandler.TABLE_BUDGET+"_"+DatabaseHandler.COLUMN_BUDGET_ALERT+", "+
						DatabaseHandler.TABLE_BUDGET+"."+DatabaseHandler.COLUMN_BUDGET_ID_CATEGORY+" AS "+DatabaseHandler.TABLE_BUDGET+"_"+DatabaseHandler.COLUMN_BUDGET_ID_CATEGORY+", "+
						DatabaseHandler.TABLE_CATEGORY+"."+DatabaseHandler.KEY_CATEGORY_NAME+" AS "+DatabaseHandler.TABLE_CATEGORY+"_"+DatabaseHandler.KEY_CATEGORY_NAME+", "+
						"SUM ("+DatabaseHandler.TABLE_TRANSACTIONS+"."+DatabaseHandler.KEY_TRANSACTION_VALUE+") AS CONSUMED " +
					" FROM "+
						DatabaseHandler.TABLE_BUDGET+
					" LEFT JOIN "+
						DatabaseHandler.TABLE_TRANSACTIONS+
					" ON "+
						DatabaseHandler.TABLE_BUDGET+"."+DatabaseHandler.COLUMN_BUDGET_ID_CATEGORY +" = "+DatabaseHandler.TABLE_TRANSACTIONS+"."+DatabaseHandler.KEY_TRANSACTION_ID_CATEGORY+
					" LEFT JOIN "+
						DatabaseHandler.TABLE_CATEGORY+
					" ON "+
						DatabaseHandler.TABLE_BUDGET+"."+DatabaseHandler.COLUMN_BUDGET_ID_CATEGORY +" = "+DatabaseHandler.TABLE_CATEGORY+"."+DatabaseHandler.KEY_CATEGORY_ID+
					" GROUP BY "+
						DatabaseHandler.TABLE_BUDGET+"."+DatabaseHandler.COLUMN_BUDGET_ID+", "+ 
						DatabaseHandler.TABLE_BUDGET+"."+DatabaseHandler.COLUMN_BUDGET_VALUE+", "+						
						DatabaseHandler.TABLE_BUDGET+"."+DatabaseHandler.COLUMN_BUDGET_NAME+", "+
						DatabaseHandler.TABLE_BUDGET+"."+DatabaseHandler.COLUMN_BUDGET_PERCENT_ALERT+", "+
						DatabaseHandler.TABLE_BUDGET+"."+DatabaseHandler.COLUMN_BUDGET_ALERT+", "+
						DatabaseHandler.TABLE_BUDGET+"."+DatabaseHandler.COLUMN_BUDGET_ID_CATEGORY+", "+
						DatabaseHandler.TABLE_CATEGORY+"."+DatabaseHandler.KEY_CATEGORY_NAME;
						

		Cursor cursor = sqlLite.rawQuery(sql, null);			
						
        if (cursor.moveToFirst()) {
        	budgets = new ArrayList<Budget>();
            while (cursor.isAfterLast() == false) {
            	int id = cursor.getInt(cursor.getColumnIndex(DatabaseHandler.TABLE_BUDGET+"_"+DatabaseHandler.COLUMN_BUDGET_ID));
            	float value = cursor.getFloat(cursor.getColumnIndex(DatabaseHandler.TABLE_BUDGET+"_"+DatabaseHandler.COLUMN_BUDGET_VALUE));
            	String name = cursor.getString(cursor.getColumnIndex(DatabaseHandler.TABLE_BUDGET+"_"+DatabaseHandler.COLUMN_BUDGET_NAME));
            	int percentAlert = cursor.getInt(cursor.getColumnIndex(DatabaseHandler.TABLE_BUDGET+"_"+DatabaseHandler.COLUMN_BUDGET_PERCENT_ALERT));
            	boolean alert = (cursor.getInt(cursor.getColumnIndex(DatabaseHandler.TABLE_BUDGET+"_"+DatabaseHandler.COLUMN_BUDGET_ALERT)) == 1?true:false);
            	int idCategory = cursor.getInt(cursor.getColumnIndex(DatabaseHandler.TABLE_BUDGET+"_"+DatabaseHandler.COLUMN_BUDGET_ID_CATEGORY));
            	float consumed = cursor.getFloat(cursor.getColumnIndex("CONSUMED"));
            	String categoryName = cursor.getString(cursor.getColumnIndex(DatabaseHandler.TABLE_CATEGORY+"_"+DatabaseHandler.KEY_CATEGORY_NAME));
                budgets.add(new Budget(id, value, name, percentAlert, alert, consumed, new Category(idCategory, categoryName)));
                cursor.moveToNext();
            }
        }
        sqlLite.close();
        cursor.close();
	    return budgets;
	}
	
	
	public long insert(Budget budget, Context context){
		SQLiteDatabase sqlLite = new DatabaseHandler(context).getReadableDatabase();
		ContentValues content = new ContentValues();
		content.put(DatabaseHandler.COLUMN_BUDGET_VALUE, budget.getValue());
		content.put(DatabaseHandler.COLUMN_BUDGET_NAME, budget.getName());
		content.put(DatabaseHandler.COLUMN_BUDGET_PERCENT_ALERT, budget.getPercentAlert());
		content.put(DatabaseHandler.COLUMN_BUDGET_ALERT, (budget.isAlert()?1:0));
		content.put(DatabaseHandler.COLUMN_BUDGET_ID_CATEGORY, budget.getCategory().getId());
		long idGenerated = sqlLite.insert(DatabaseHandler.TABLE_BUDGET, null, content);
		sqlLite.close();
		return idGenerated;
	}
	
	public int update(Budget budget, Context context)  {
		SQLiteDatabase sqlLite = new DatabaseHandler(context).getWritableDatabase();
		ContentValues content = new ContentValues();
		content.put(DatabaseHandler.COLUMN_BUDGET_VALUE, budget.getValue());
		content.put(DatabaseHandler.COLUMN_BUDGET_NAME, budget.getName());
		content.put(DatabaseHandler.COLUMN_BUDGET_PERCENT_ALERT, budget.getPercentAlert());
		content.put(DatabaseHandler.COLUMN_BUDGET_ALERT, (budget.isAlert()?1:0));
		content.put(DatabaseHandler.COLUMN_BUDGET_ID_CATEGORY, budget.getCategory().getId());
        String where = DatabaseHandler.COLUMN_BUDGET_ID+" = ?";
        String argumentos[] = { String.valueOf(budget.getId()) };
        int idGenerated = sqlLite.update(DatabaseHandler.TABLE_BUDGET, content, where, argumentos);
		sqlLite.close();
		return idGenerated;
	}
	
	public int delete(Budget budget, Context context) {
        SQLiteDatabase sqlLite = new DatabaseHandler(context).getWritableDatabase(); 
		String where = DatabaseHandler.COLUMN_BUDGET_ID+" = ? ";
		String[] argumentos = new String[] { String.valueOf(budget.getId()) };
		return sqlLite.delete(DatabaseHandler.TABLE_BUDGET, where, argumentos);
	}
	
	public Budget getByIdCategory(long idCategory, Context context){
		Budget budget = null;
		String sql = "SELECT "+
				DatabaseHandler.TABLE_BUDGET+"."+DatabaseHandler.COLUMN_BUDGET_ID+" AS "+DatabaseHandler.TABLE_BUDGET+"_"+DatabaseHandler.COLUMN_BUDGET_ID+", "+ 
				DatabaseHandler.TABLE_BUDGET+"."+DatabaseHandler.COLUMN_BUDGET_VALUE+" AS "+DatabaseHandler.TABLE_BUDGET+"_"+DatabaseHandler.COLUMN_BUDGET_VALUE+", "+						
				DatabaseHandler.TABLE_BUDGET+"."+DatabaseHandler.COLUMN_BUDGET_NAME+" AS "+DatabaseHandler.TABLE_BUDGET+"_"+DatabaseHandler.COLUMN_BUDGET_NAME+", "+
				DatabaseHandler.TABLE_BUDGET+"."+DatabaseHandler.COLUMN_BUDGET_PERCENT_ALERT+" AS "+DatabaseHandler.TABLE_BUDGET+"_"+DatabaseHandler.COLUMN_BUDGET_PERCENT_ALERT+", "+
				DatabaseHandler.TABLE_BUDGET+"."+DatabaseHandler.COLUMN_BUDGET_ALERT+" AS "+DatabaseHandler.TABLE_BUDGET+"_"+DatabaseHandler.COLUMN_BUDGET_ALERT+", "+
				DatabaseHandler.TABLE_BUDGET+"."+DatabaseHandler.COLUMN_BUDGET_ID_CATEGORY+" AS "+DatabaseHandler.TABLE_BUDGET+"_"+DatabaseHandler.COLUMN_BUDGET_ID_CATEGORY+", "+
				DatabaseHandler.TABLE_CATEGORY+"."+DatabaseHandler.KEY_CATEGORY_NAME+" AS "+DatabaseHandler.TABLE_CATEGORY+"_"+DatabaseHandler.KEY_CATEGORY_NAME+", "+
				"SUM ("+DatabaseHandler.TABLE_TRANSACTIONS+"."+DatabaseHandler.KEY_TRANSACTION_VALUE+") AS CONSUMED " +
			" FROM "+
				DatabaseHandler.TABLE_BUDGET+", "+
				DatabaseHandler.TABLE_CATEGORY+", "+
				DatabaseHandler.TABLE_TRANSACTIONS+
			" WHERE "+DatabaseHandler.TABLE_BUDGET+"."+DatabaseHandler.COLUMN_BUDGET_ID_CATEGORY+"="+idCategory+
			" AND "+DatabaseHandler.TABLE_TRANSACTIONS+"."+DatabaseHandler.KEY_TRANSACTION_ID_CATEGORY +" = "+idCategory+
			" AND "+DatabaseHandler.TABLE_CATEGORY+"."+DatabaseHandler.KEY_CATEGORY_ID+" = "+idCategory+
//			" LEFT JOIN "+
//				DatabaseHandler.TABLE_TRANSACTIONS+
//			" ON "+
//				DatabaseHandler.TABLE_TRANSACTIONS+"."+DatabaseHandler.KEY_TRANSACTION_ID_CATEGORY +" = "+idCategory+
//			" LEFT JOIN "+
//				DatabaseHandler.TABLE_CATEGORY+
//			" ON "+
//				DatabaseHandler.TABLE_CATEGORY+"."+DatabaseHandler.KEY_CATEGORY_ID+" = "+idCategory+
			" GROUP BY "+
				DatabaseHandler.TABLE_BUDGET+"."+DatabaseHandler.COLUMN_BUDGET_ID+", "+ 
				DatabaseHandler.TABLE_BUDGET+"."+DatabaseHandler.COLUMN_BUDGET_VALUE+", "+						
				DatabaseHandler.TABLE_BUDGET+"."+DatabaseHandler.COLUMN_BUDGET_NAME+", "+
				DatabaseHandler.TABLE_BUDGET+"."+DatabaseHandler.COLUMN_BUDGET_PERCENT_ALERT+", "+
				DatabaseHandler.TABLE_BUDGET+"."+DatabaseHandler.COLUMN_BUDGET_ALERT+", "+
				DatabaseHandler.TABLE_BUDGET+"."+DatabaseHandler.COLUMN_BUDGET_ID_CATEGORY+", "+
				DatabaseHandler.TABLE_CATEGORY+"."+DatabaseHandler.KEY_CATEGORY_NAME;
		
		SQLiteDatabase sqlLite = new DatabaseHandler(context).getReadableDatabase();
		Cursor cursor = sqlLite.rawQuery(sql, null);			
		
        if (cursor.moveToFirst()) {
        	
            if (cursor.isAfterLast() == false) {
            	int id = cursor.getInt(cursor.getColumnIndex(DatabaseHandler.TABLE_BUDGET+"_"+DatabaseHandler.COLUMN_BUDGET_ID));
            	float value = cursor.getFloat(cursor.getColumnIndex(DatabaseHandler.TABLE_BUDGET+"_"+DatabaseHandler.COLUMN_BUDGET_VALUE));
            	String name = cursor.getString(cursor.getColumnIndex(DatabaseHandler.TABLE_BUDGET+"_"+DatabaseHandler.COLUMN_BUDGET_NAME));
            	int percentAlert = cursor.getInt(cursor.getColumnIndex(DatabaseHandler.TABLE_BUDGET+"_"+DatabaseHandler.COLUMN_BUDGET_PERCENT_ALERT));
            	boolean alert = (cursor.getInt(cursor.getColumnIndex(DatabaseHandler.TABLE_BUDGET+"_"+DatabaseHandler.COLUMN_BUDGET_ALERT)) == 1?true:false);
            	//int idCategory = cursor.getInt(cursor.getColumnIndex(DatabaseHandler.TABLE_BUDGET+"_"+DatabaseHandler.COLUMN_BUDGET_ID_CATEGORY));
            	float consumed = cursor.getFloat(cursor.getColumnIndex("CONSUMED"));
            	String categoryName = cursor.getString(cursor.getColumnIndex(DatabaseHandler.TABLE_CATEGORY+"_"+DatabaseHandler.KEY_CATEGORY_NAME));
            	budget = new Budget(id, value, name, percentAlert, alert, consumed, new Category(idCategory, categoryName));
            }
        }
		
		return budget;
	}
	
	
}
